package com.centit.cxtj.dao;

import java.util.HashMap;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.centit.airplanebaseinfo.po.AirplaneBaseinfo;
import com.centit.framework.core.dao.CodeBook;
import com.centit.framework.jdbc.dao.BaseDaoImpl;
import com.centit.framework.jdbc.dao.DatabaseOptUtils;
import com.centit.support.database.utils.PageDesc;
import com.centit.support.database.utils.QueryAndNamedParams;
import com.centit.support.database.utils.QueryUtils;

/**
 * 	创建这个类是为了避免和airplanebaseinfo相关代码的冲突问题，毕竟两个人都在同时修改
 * @author 曹顺
 *
 */
@Repository
public class Cxtjdao  extends BaseDaoImpl<AirplaneBaseinfo,java.lang.String> {

	@Override
	public Map<String, String> getFilterField() {
		if( filterField == null){
			filterField = new HashMap<String, String>();

			filterField.put("recId" , CodeBook.EQUAL_HQL_ID);


			filterField.put("gljh" , CodeBook.EQUAL_HQL_ID);

			filterField.put("ccxlh" , CodeBook.LIKE_HQL_ID);

			filterField.put("zch" , CodeBook.EQUAL_HQL_ID);

			filterField.put("fsn" , CodeBook.EQUAL_HQL_ID);

			filterField.put("airZt" , CodeBook.EQUAL_HQL_ID);

			filterField.put("nature" , CodeBook.EQUAL_HQL_ID);

			filterField.put("pw" , CodeBook.EQUAL_HQL_ID);

			filterField.put("startTime" , CodeBook.EQUAL_HQL_ID);

			filterField.put("endTime" , CodeBook.EQUAL_HQL_ID);

			filterField.put("sign" , CodeBook.LIKE_HQL_ID);
			
			filterField.put("signList" , "[ :signList | AND sign in (:signList) ]");

			filterField.put("airModel" , CodeBook.LIKE_HQL_ID);

			filterField.put("airZzs" , CodeBook.EQUAL_HQL_ID);

			filterField.put("outSn" , CodeBook.EQUAL_HQL_ID);

			filterField.put("proctionDate" , CodeBook.EQUAL_HQL_ID);

			filterField.put("useDate" , CodeBook.EQUAL_HQL_ID);

			filterField.put("offDate" , CodeBook.EQUAL_HQL_ID);

			filterField.put("engineS" , CodeBook.EQUAL_HQL_ID);

			filterField.put("engine" , CodeBook.EQUAL_HQL_ID);

			filterField.put("oneSn" , CodeBook.EQUAL_HQL_ID);

			filterField.put("twoSn" , CodeBook.EQUAL_HQL_ID);

			filterField.put("gjNo" , CodeBook.EQUAL_HQL_ID);

			filterField.put("gjDate" , CodeBook.EQUAL_HQL_ID);

			filterField.put("bzfj" , CodeBook.EQUAL_HQL_ID);

			filterField.put("haveName" , CodeBook.EQUAL_HQL_ID);

			filterField.put("haveAddr" , CodeBook.EQUAL_HQL_ID);

			filterField.put("yyName" , CodeBook.EQUAL_HQL_ID);

			filterField.put("yyAddr" , CodeBook.EQUAL_HQL_ID);

			filterField.put("shNo" , CodeBook.EQUAL_HQL_ID);

			filterField.put("qfDate" , CodeBook.EQUAL_HQL_ID);

			filterField.put("hjNo" , CodeBook.EQUAL_HQL_ID);

			filterField.put("addrNoHex" , CodeBook.EQUAL_HQL_ID);

			filterField.put("addrNoOct" , CodeBook.EQUAL_HQL_ID);

			filterField.put("dtNo" , CodeBook.EQUAL_HQL_ID);

			filterField.put("dtyxDate" , CodeBook.EQUAL_HQL_ID);

			filterField.put("dtbfDate" , CodeBook.EQUAL_HQL_ID);

			filterField.put("dtz" , CodeBook.EQUAL_HQL_ID);

			filterField.put("shz" , CodeBook.EQUAL_HQL_ID);

			filterField.put("gjz" , CodeBook.EQUAL_HQL_ID);

			filterField.put("ht" , CodeBook.EQUAL_HQL_ID);

			filterField.put("ht2htsqxx" , CodeBook.EQUAL_HQL_ID);

			filterField.put("fjyz" , CodeBook.EQUAL_HQL_ID);

			filterField.put("fjcz" , CodeBook.EQUAL_HQL_ID);

			filterField.put("bxxx" , CodeBook.EQUAL_HQL_ID);

			filterField.put("zjbz" , CodeBook.EQUAL_HQL_ID);

			filterField.put("mqzj" , CodeBook.EQUAL_HQL_ID);

			filterField.put("zjzfqj" , CodeBook.EQUAL_HQL_ID);

			filterField.put("hl" , CodeBook.EQUAL_HQL_ID);

			filterField.put("jrygrq" , CodeBook.EQUAL_HQL_ID);

			filterField.put("tcygrq" , CodeBook.EQUAL_HQL_ID);

			filterField.put("apu" , CodeBook.EQUAL_HQL_ID);

			filterField.put("hud" , CodeBook.EQUAL_HQL_ID);

			filterField.put("wifi" , CodeBook.EQUAL_HQL_ID);

			filterField.put("maxZl" , CodeBook.EQUAL_HQL_ID);

			filterField.put("zyqdj" , CodeBook.EQUAL_HQL_ID);

			filterField.put("idera" , CodeBook.EQUAL_HQL_ID);

			filterField.put("zwbj" , CodeBook.EQUAL_HQL_ID);
			
			filterField.put("djsj" , CodeBook.EQUAL_HQL_ID);
			
			filterField.put("status" , CodeBook.EQUAL_HQL_ID);

			filterField.put("dataValid" , CodeBook.EQUAL_HQL_ID);

			filterField.put("insUsercode" , CodeBook.EQUAL_HQL_ID);

			filterField.put("insTime" , CodeBook.EQUAL_HQL_ID);

			filterField.put("editUsercode" , CodeBook.EQUAL_HQL_ID);

			filterField.put("editTime" , CodeBook.EQUAL_HQL_ID);

			filterField.put("delUsercode" , CodeBook.EQUAL_HQL_ID);

			filterField.put("delTime" , CodeBook.EQUAL_HQL_ID);
			
			filterField.put("szdqsj" , "[:szdqsj | AND TO_NUMBER(DTYX_DATE-SYSDATE) <= :szdqsj]");
			
			filterField.put("htdqsj" , "[:htdqsj | AND END_TIME IS NOT NULL AND TO_NUMBER(END_TIME-SYSDATE) <=:htdqsj ]");

		}
		return filterField;
	} 

	
	/**
	 * 	飞机引进情况---- 统计报表
	 * @param filterMap
	 * @param pageDesc
	 * @return
	 */
	public JSONArray getFjyjqlList(Map<String, Object> filterMap, PageDesc pageDesc) {
		String sql = "WITH airtable AS (SELECT extract (year from TO_DATE(DJSJ,'YYYY-MM-DD HH24:MI:SS')) nf ,AIR_MODEL jx,sign zch,DJSJ yjsj,remark bz,sign FROM T_AIRPLANE_BASEINFO WHERE 1=1 [ :status |  and STATUS = :status])"
				+ "SELECT * FROM airtable WHERE 1=1 "
				+ "[ :nf | and nf > (extract (year from SYSDATE)-:nf)]"
				+ "[ :starnf | and nf >= :starnf ]"
				+ "[ :endnf | and nf <= :endnf ]"
				+ " ORDER BY jx ASC";
		QueryAndNamedParams params = QueryUtils.translateQuery(sql, filterMap);
		JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(), pageDesc);
		if (jsonArray == null) {
			return new JSONArray();
		}
		return jsonArray;
	}
	
	
	/**
	 * 	在用飞机信息情况---- 统计报表
	 * @param filterMap
	 * @param pageDesc
	 * @return
	 */
	public JSONArray fjxxqk(Map<String, Object> filterMap) {
		String sql = "WITH airtable AS (SELECT extract (year from TO_DATE(DJSJ,'YYYY-MM-DD HH24:MI:SS')) nf ,AIR_MODEL jx,sign zch,PROCTION_DATE ccsj,DJSJ yjsj,ROUND((SYSDATE-PROCTION_DATE)/365,2) jl ,b.DATA_VALUE xz,a.remark bz FROM T_AIRPLANE_BASEINFO a LEFT JOIN F_DATADICTIONARY b ON a.nature = b.DATA_CODE AND b.CATALOG_CODE = 'AIRXZ' WHERE a.STATUS = '2')"
				+ "SELECT * FROM airtable WHERE 1=1  "
				+ "[ :nf | and nf > (extract (year from SYSDATE)-:nf)]"
				+ "[ :starnf | and nf >= :starnf ]"
				+ "[ :endnf | and nf <= :endnf ]"
				+ " ORDER BY jx ASC";
		QueryAndNamedParams params = QueryUtils.translateQuery(sql, filterMap);
		JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(),null);
		if (jsonArray == null) {
			return new JSONArray();
		}
		return jsonArray;
	}
	
	/**
	 * 	在用飞机信息情况 合计表统计---- 统计报表
	 * @param filterMap
	 * @param pageDesc
	 * @return
	 */
	public JSONArray fjxxqkhj(Map<String, Object> filterMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("WITH pjjltable AS (SELECT ROUND ( AVG ((SYSDATE - PROCTION_DATE) / 365),2) pjjl FROM T_AIRPLANE_BASEINFO WHERE status = '2' [ :nf | and extract (year from TO_DATE(DJSJ,'YYYY-MM-DD HH24:MI:SS')) > (extract (year from SYSDATE)-:nf)]  [ :starnf | and extract (year from TO_DATE(DJSJ,'YYYY-MM-DD HH24:MI:SS')) >= :starnf ]  [ :endnf | and extract (year from TO_DATE(DJSJ,'YYYY-MM-DD HH24:MI:SS')) <= :endnf ] ),hjtable AS (SELECT COUNT (0) hj FROM T_AIRPLANE_BASEINFO  WHERE status = '2' [ :nf | and extract (year from TO_DATE(DJSJ,'YYYY-MM-DD HH24:MI:SS')) > (extract (year from SYSDATE)-:nf)]  [ :starnf | and extract (year from TO_DATE(DJSJ,'YYYY-MM-DD HH24:MI:SS')) >= :starnf ]  [ :endnf | and extract (year from TO_DATE(DJSJ,'YYYY-MM-DD HH24:MI:SS')) <= :endnf ] )");
		sb.append(" SELECT AIR_MODEL xh ,COUNT (0) num,(SELECT pjjl FROM pjjltable) pjjl,(SELECT hj FROM hjtable) hj,TO_CHAR (SYSDATE, 'yyyy-mm-dd') gxrq FROM T_AIRPLANE_BASEINFO WHERE status = '2' [ :nf | and extract (year from TO_DATE(DJSJ,'YYYY-MM-DD HH24:MI:SS')) > (extract (year from SYSDATE)-:nf)]  [ :starnf | and extract (year from TO_DATE(DJSJ,'YYYY-MM-DD HH24:MI:SS')) >= :starnf ]  [ :endnf | and extract (year from TO_DATE(DJSJ,'YYYY-MM-DD HH24:MI:SS')) <= :endnf ]   GROUP BY AIR_MODEL  order by AIR_MODEL asc");
		QueryAndNamedParams params = QueryUtils.translateQuery(sb.toString(), filterMap);
		JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(),null);
		if (jsonArray == null) {
			return new JSONArray();
		}
		return jsonArray;
	}
	
	
	/**
	 * 	机龄分析--统计分析
	 * @param filterMap
	 * @param pageDesc
	 * @return
	 */
	public JSONArray getJlfxList(Map<String, Object> filterMap) {
		String sql = "WITH ageTable AS (SELECT floor(months_between(SYSDATE, PROCTION_DATE) / 12) age FROM T_AIRPLANE_BASEINFO WHERE status = '2')SELECT SUM(CASE WHEN age >=0 AND age < 5 THEN 1 else 0 END) AS zerotofive,SUM(CASE WHEN age >=5 AND age < 10 THEN 1 else 0 END) AS fivetoten,SUM(CASE WHEN age >=10 AND age < 15 THEN 1 else 0 END) AS tentofifteen FROM ageTable";
		QueryAndNamedParams params = QueryUtils.translateQuery(sql, filterMap);
		JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(), null);
		if (jsonArray == null) {
			return new JSONArray();
		}
		return jsonArray;
	}
	
	
	/**
	 * 	飞机租赁性质分析--统计分析
	 * @param filterMap
	 * @param pageDesc
	 * @return
	 */
	public JSONArray getFjzlxzList(Map<String, Object> filterMap) {
		//"SELECT AIR_MODEL,sign,nature,DT_NO,DTYX_DATE,TO_NUMBER(SYSDATE-DTYX_DATE) FROM T_AIRPLANE_BASEINFO WHERE DTYX_DATE IS NOT NULL"; 飞机的电台执照到期数据
		String sql = "SELECT a.DATA_CODE,a.DATA_VALUE as nature,NVL(b.num,0) as num FROM F_DATADICTIONARY a  LEFT JOIN (SELECT COUNT(0) num,NATURE FROM T_AIRPLANE_BASEINFO WHERE status='2' GROUP BY NATURE) b ON a.DATA_CODE = b.NATURE WHERE CATALOG_CODE = 'AIRXZ'";
		QueryAndNamedParams params = QueryUtils.translateQuery(sql, filterMap);
		JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(), null);
		if (jsonArray == null) {
			return new JSONArray();
		}
		return jsonArray;
	}
	
	
	
	
	/**
	 * 	首页上方数据--统计分析
	 * @param filterMap
	 * @param pageDesc
	 * @return
	 */
	public JSONArray homePageTopNum(Map<String, Object> filterMap) {
		String sql= "SELECT SUM(CASE WHEN AIR_ZT = '1' THEN 1 ELSE 0 END) jhjf,SUM(CASE WHEN AIR_ZT = '2' THEN 1 ELSE 0 END) sjyy,SUM(CASE WHEN AIR_ZT = '3' THEN 1 ELSE 0 END) tcyy FROM T_AIRPLANE_BASEINFO WHERE DATA_VALID = 'T'";
		QueryAndNamedParams params = QueryUtils.translateQuery(sql, filterMap);
		JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(), null);
		if (jsonArray == null) {
			return new JSONArray();
		}
		return jsonArray;
	}
	
	
	/**
	 * 	机型列表
	 * @param filterMap
	 * @param pageDesc
	 * @return
	 */
	public JSONArray jxList() {
		String sql= "SELECT AIR_MODEL FROM T_AIRPLANE_BASEINFO where AIR_MODEL is not null GROUP BY AIR_MODEL";
		QueryAndNamedParams params = QueryUtils.translateQuery(sql, new HashMap<>());
		JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(), null);
		if (jsonArray == null) {
			return new JSONArray();
		}
		return jsonArray;
	}
	
	/**
	 * 	所有飞机的年份跨度列表
	 * @return
	 */
	public JSONArray nfList(Map<String, Object> filterMap) {
		String sql = "";
		if(filterMap.containsKey("qj") && StringUtils.isNoneBlank(filterMap.get("qj").toString())) {
			int cs = Integer.valueOf(filterMap.get("qj").toString());
			sql = "SELECT EXTRACT(YEAR FROM SYSDATE) - LEVEL +1  nf FROM dual CONNECT BY LEVEL <= "+cs+" ORDER BY nf ASC";
		} else {
			if(filterMap.containsKey("starnf") && StringUtils.isNoneBlank(filterMap.get("starnf").toString()) && filterMap.containsKey("endnf") && StringUtils.isNoneBlank(filterMap.get("endnf").toString()) ) {
				int ksn = Integer.valueOf(filterMap.get("starnf").toString());
				int jsn = Integer.valueOf(filterMap.get("endnf").toString());
				sql= "WITH nfBasetable AS (SELECT "+ksn+" ksn,"+(jsn-ksn)+" AS czn FROM dual) SELECT (SELECT ksn FROM nfBasetable)+level-1 nf FROM DUAL CONNECT BY LEVEL <=(SELECT czn FROM nfBasetable)+1";
			} else {
				sql= "WITH nfBasetable AS (SELECT MIN(extract(year from START_TIME)) ksn,MAX(extract(year from SYSDATE))-MIN(extract(year from START_TIME)) AS czn FROM T_AIRPLANE_BASEINFO where status>1) SELECT (SELECT ksn FROM nfBasetable)+level-1 nf FROM DUAL CONNECT BY LEVEL <=(SELECT czn FROM nfBasetable)+1";
			}
		}
		QueryAndNamedParams params = QueryUtils.translateQuery(sql, new HashMap<>());
		JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(), null);
		if (jsonArray == null) {
			return new JSONArray();
		}
		return jsonArray;
	}
	
	/**
	 * 	飞机引进分析
	 * @return
	 */
	public JSONArray fjyjfx(Map<String, Object> filterMap) {
		String sql = "WITH tctable AS (SELECT extract (year from OFF_DATE) nf FROM T_AIRPLANE_BASEINFO WHERE OFF_DATE IS NOT NULL AND STATUS = '3'),yjtable AS (SELECT extract (year from TO_DATE(DJSJ,'YYYY-MM-DD HH24:MI:SS')) nf FROM T_AIRPLANE_BASEINFO WHERE DJSJ IS NOT NULL AND STATUS = '2')"
				+ "SELECT nf,COUNT(0) num,0 type from tctable WHERE 1=1 [ :qj | AND nf > (EXTRACT (YEAR FROM SYSDATE) -:qj) ] [:starnf | AND nf >= :starnf ] [:endnf | AND nf <= :endnf ] GROUP BY nf UNION SELECT nf,COUNT(0) num,1 type from yjtable  WHERE 1=1 [ :qj | AND nf > (EXTRACT (YEAR FROM SYSDATE) -:qj) ] [:starnf | AND nf >= :starnf ] [:endnf | AND nf <= :endnf ]  GROUP BY nf";
		QueryAndNamedParams params = QueryUtils.translateQuery(sql, filterMap);
		JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(), null);
		if (jsonArray == null) {
			return new JSONArray();
		}
		return jsonArray;
	}
	
	public JSONArray listAirPlain(Map<String, Object> filterMap) {
		String sql = "SELECT a.*,b.GH_NAME as gljhtext FROM T_AIRPLANE_BASEINFO a LEFT JOIN T_JDGHGL b ON a.GLJH = b.REC_ID WHERE 1=1 "
				+ "[ :status | AND A.STATUS = :status ]"
				+ "[ :dataValid | AND A.DATA_VALID = :dataValid ]";
		QueryAndNamedParams params = QueryUtils.translateQuery(sql, filterMap);
		JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(), null);
		if (jsonArray == null) {
			return new JSONArray();
		}
		return jsonArray;
	}
	
}
